package excel.util;

import excel.pojo.User;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.http.HttpResponse;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * @author 秉性哥
 * @createTime 2022/10/28 9:04
 * @description 导出工具类
 */
public class ExportUtils {
    public static void exportExcel(Integer lastSheet, String[] handers, List<User> list, String filePath) throws IOException {
        //创建工作簿
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建表单sheet
        for (Integer j = 0; j < lastSheet; j++) {
            Sheet sheet = wb.createSheet("第" + (j + 1) + "页");
            Row rowFirst = sheet.createRow(0);
            addRowFirst(rowFirst);
            /*switch (j + 1) {
                case 1:*/
                    for (int i = 0; i < list.size(); i++) {
                        User user = list.get(i);
                        //创建行对象  参数：索引（从0开始）
                        Row row = sheet.createRow(i + 1);
                        //设置对应单元格的值
                        row.setHeight((short) 400);   // 设置每行的高度
                        //"序号","用户名","密码","结束时间","失败次数"
                        if (user.getId() != null) {
                            row.createCell(0).setCellValue(user.getId());
                        }
                        if (user.getUsername() != null) {
                            row.createCell(1).setCellValue(user.getUsername());
                        }
                        if (user.getPassword() != null) {
                            row.createCell(2).setCellValue(user.getPassword());
                        }
                        if (user.getEnd_login_date() != null) {
                            row.createCell(3).setCellValue(user.getEnd_login_date());
                        }
                        if (user.getFail_count() != null) {
                            row.createCell(4).setCellValue(user.getFail_count());
                        }
                    }
                   /* break;
                case 2:
                    for (int i = 0; i < list2.size(); i++) {
                        User user = list2.get(i);
                        //创建行对象  参数：索引（从0开始）
                        Row row = sheet.createRow(i + 1);
                        //设置对应单元格的值
                        row.setHeight((short) 400);   // 设置每行的高度
                        //"序号","用户名","密码","结束时间","失败次数"
                        if (user.getId() != null) {
                            row.createCell(0).setCellValue(user.getId());
                        }
                        if (user.getUsername() != null) {
                            row.createCell(1).setCellValue(user.getUsername());
                        }
                        if (user.getPassword() != null) {
                            row.createCell(2).setCellValue(user.getPassword());
                        }
                        if (user.getEnd_login_date() != null) {
                            row.createCell(3).setCellValue(user.getEnd_login_date());
                        }
                        if (user.getFail_count() != null) {
                            row.createCell(4).setCellValue(user.getFail_count());
                        }
                    }
                    break;
                case 3:
                    for (int i = 0; i < list3.size(); i++) {
                        User user = list3.get(i);
                        //创建行对象  参数：索引（从0开始）
                        Row row = sheet.createRow(i + 1);
                        //设置对应单元格的值
                        row.setHeight((short) 400);   // 设置每行的高度
                        //"序号","用户名","密码","结束时间","失败次数"
                        if (user.getId() != null) {
                            row.createCell(0).setCellValue(user.getId());
                        }
                        if (user.getUsername() != null) {
                            row.createCell(1).setCellValue(user.getUsername());
                        }
                        if (user.getPassword() != null) {
                            row.createCell(2).setCellValue(user.getPassword());
                        }
                        if (user.getEnd_login_date() != null) {
                            row.createCell(3).setCellValue(user.getEnd_login_date());
                        }
                        if (user.getFail_count() != null) {
                            row.createCell(4).setCellValue(user.getFail_count());
                        }
                    }
                    break;
            }*/
        }

       /* //第一个sheet第一行为标题

        //写标题
        for (int i = 0; i < handers.length; i++) {

        }*/


        //样式处理
        //创建样式对象
        /*CellStyle style = wb.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        //创建字体对象
        Font font = wb.createFont();
        font.setFontName("宋体"); //字体
        font.setFontHeightInPoints((short)25);//字号
        style.setFont(font);
*/
        //行高和列宽
        //.setHeightInPoints(30);//行高
        //列宽的宽度  字符宽度
        //   sheet.setColumnWidth(2,31 * 256);//列宽

        //剧中显示
        // style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        // style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        //向单元格设置样式
        //  cell.setCellStyle(style);
      /*  response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=test.zip");
        ServletOutputStream out = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(out);
        ZipEntry z = new ZipEntry("test.zip");
        zipOutputStream.putNextEntry(z);
        //写入一个压缩文件---最后写文件
        wb.write(zipOutputStream);
        zipOutputStream.close();
        out.close();*/

        //文件流
        FileOutputStream pis = new FileOutputStream(filePath);
        //写入文件
        wb.write(pis);
        pis.close();
    }

    public static void addRowFirst(Row rowFirst) {
        Cell cell0 = rowFirst.createCell(0);
        Cell cell1 = rowFirst.createCell(1);
        Cell cell2 = rowFirst.createCell(2);
        Cell cell3 = rowFirst.createCell(3);
        Cell cell4 = rowFirst.createCell(4);
        cell0.setCellValue("序号");
        cell1.setCellValue("用户名");
        cell2.setCellValue("密码");
        cell3.setCellValue("结束时间");
        cell4.setCellValue("失败次数");
    }


    public static void exportCSVFile(HttpServletResponse response,List<User> list,String[] HANDERS) {
        // 文件名
        String filename = "导出" + new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date()) + ".csv";
        // CSV文件每一列的名称
        String[] colNames = HANDERS ;
        File file=new File(filename);
        CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader(colNames);
        try (OutputStreamWriter osw = new OutputStreamWriter(new FileOutputStream(file), "UTF-8");
             CSVPrinter csvPrinter = new CSVPrinter(osw, csvFormat);) {
            for (User user:list){
                csvPrinter.printRecord(user.getId(),user.getUsername(),user.getPassword(),user.getEnd_login_date(),user.getFail_count());
            }
            csvPrinter.flush();
        }catch(IOException e){
            e.printStackTrace();
        }
        InputStream in = null;
        OutputStream out = null;
        try {
            in = new FileInputStream(file);
            int len = 0;
            byte[] buffer = new byte[1024];
            out = response.getOutputStream();
            response.reset();
            // 设置此response为文件下载响应
            response.setContentType("application/csv;charset=UTF-8");
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + URLEncoder.encode(file.getName(), "UTF-8"));
            response.setCharacterEncoding("UTF-8");

            // 先写UTF-8文件标志头
            out.write(new byte[] { (byte) 0xEF, (byte) 0xBB, (byte) 0xBF });
            while ((len = in.read(buffer)) > 0) {
                out.write(buffer, 0, len);
            }
            out.flush();
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                in.close();
                out.close();
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void exportExcelZip(HttpServletResponse response,Integer lastSheet, String[] handers, List<User> list1, List<User> list2, List<User> list3, String filePath) throws IOException {
        //创建工作簿
        Workbook wb = new XSSFWorkbook(); //2007版本
        //创建表单sheet
        for (Integer j = 0; j < lastSheet; j++) {
            Sheet sheet = wb.createSheet("第" + (j + 1) + "页");
            Row rowFirst = sheet.createRow(0);
            addRowFirst(rowFirst);
            switch (j + 1) {
                case 1:
                    for (int i = 0; i < list1.size(); i++) {
                        User user = list1.get(i);
                        //创建行对象  参数：索引（从0开始）
                        Row row = sheet.createRow(i + 1);
                        //设置对应单元格的值
                        row.setHeight((short) 400);   // 设置每行的高度
                        //"序号","用户名","密码","结束时间","失败次数"
                        if (user.getId() != null) {
                            row.createCell(0).setCellValue(user.getId());
                        }
                        if (user.getUsername() != null) {
                            row.createCell(1).setCellValue(user.getUsername());
                        }
                        if (user.getPassword() != null) {
                            row.createCell(2).setCellValue(user.getPassword());
                        }
                        if (user.getEnd_login_date() != null) {
                            row.createCell(3).setCellValue(user.getEnd_login_date());
                        }
                        if (user.getFail_count() != null) {
                            row.createCell(4).setCellValue(user.getFail_count());
                        }
                    }
                    break;
                case 2:
                    for (int i = 0; i < list2.size(); i++) {
                        User user = list2.get(i);
                        //创建行对象  参数：索引（从0开始）
                        Row row = sheet.createRow(i + 1);
                        //设置对应单元格的值
                        row.setHeight((short) 400);   // 设置每行的高度
                        //"序号","用户名","密码","结束时间","失败次数"
                        if (user.getId() != null) {
                            row.createCell(0).setCellValue(user.getId());
                        }
                        if (user.getUsername() != null) {
                            row.createCell(1).setCellValue(user.getUsername());
                        }
                        if (user.getPassword() != null) {
                            row.createCell(2).setCellValue(user.getPassword());
                        }
                        if (user.getEnd_login_date() != null) {
                            row.createCell(3).setCellValue(user.getEnd_login_date());
                        }
                        if (user.getFail_count() != null) {
                            row.createCell(4).setCellValue(user.getFail_count());
                        }
                    }
                    break;
                case 3:
                    for (int i = 0; i < list3.size(); i++) {
                        User user = list3.get(i);
                        //创建行对象  参数：索引（从0开始）
                        Row row = sheet.createRow(i + 1);
                        //设置对应单元格的值
                        row.setHeight((short) 400);   // 设置每行的高度
                        //"序号","用户名","密码","结束时间","失败次数"
                        if (user.getId() != null) {
                            row.createCell(0).setCellValue(user.getId());
                        }
                        if (user.getUsername() != null) {
                            row.createCell(1).setCellValue(user.getUsername());
                        }
                        if (user.getPassword() != null) {
                            row.createCell(2).setCellValue(user.getPassword());
                        }
                        if (user.getEnd_login_date() != null) {
                            row.createCell(3).setCellValue(user.getEnd_login_date());
                        }
                        if (user.getFail_count() != null) {
                            row.createCell(4).setCellValue(user.getFail_count());
                        }
                    }
                    break;
            }
        }

       /* //第一个sheet第一行为标题

        //写标题
        for (int i = 0; i < handers.length; i++) {

        }*/


        //样式处理
        //创建样式对象
        /*CellStyle style = wb.createCellStyle();
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        //创建字体对象
        Font font = wb.createFont();
        font.setFontName("宋体"); //字体
        font.setFontHeightInPoints((short)25);//字号
        style.setFont(font);
*/
        //行高和列宽
        //.setHeightInPoints(30);//行高
        //列宽的宽度  字符宽度
        //   sheet.setColumnWidth(2,31 * 256);//列宽

        //剧中显示
        // style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        // style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

        //向单元格设置样式
        //  cell.setCellStyle(style);
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=导出zip.zip");
        ServletOutputStream out = response.getOutputStream();
        ZipOutputStream zipOutputStream = new ZipOutputStream(out);
        ZipEntry z = new ZipEntry(filePath);
        zipOutputStream.putNextEntry(z);
        //写入一个压缩文件---最后写文件
        wb.write(zipOutputStream);
        zipOutputStream.close();
        out.close();

        //文件流
        FileOutputStream pis = new FileOutputStream(filePath);
        //写入文件
        wb.write(pis);
        pis.close();
    }

}
